/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.Cliente;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author iron
 */
public class ClienteDAO {
    
    public static final ClienteDAO INSTANCE = new ClienteDAO();
    BancoDeDados bd = bd = new BancoDeDados();
    
    
    public Cliente findByCPF(Long cpf) throws SQLException, Exception{
        bd.conectar();
        Cliente cliente = null;
        bd.sql = "SELECT * FROM \"Cliente\" WHERE \"cpf\" = ?";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setLong(1, cpf);
        ResultSet rs = bd.comandoSql.executeQuery();         
        while(rs.next()){            
            String nome = rs.getString(2);
            String telefone = rs.getString(3);
            cliente = new Cliente(nome,telefone,cpf);           
        }
        bd.desconectar();
        
        return cliente;
    }
    
    public Cliente findByNome(String nome) throws SQLException, Exception{
        bd.conectar();
        Cliente cliente = null;
        bd.sql = "SELECT * FROM \"Cliente\" WHERE \"nome\" = ?";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        bd.comandoSql.setString(1, nome);
        ResultSet rs = bd.comandoSql.executeQuery();         
        rs.next();            
        Long cpf = rs.getLong(1);
        String telefone = rs.getString(3);
        cliente = new Cliente(nome,telefone,cpf);           
        bd.desconectar();
        
        return cliente;
    }
    
    public void delete(Long cpf) throws SQLException, Exception{
            bd.conectar();
            bd.sql = "DELETE FROM \"Cliente\" where \"cpf\" = ?";
            bd.comandoSql = bd.connection.prepareStatement(bd.sql);
            bd.comandoSql.setLong(1, cpf);
            bd.comandoSql.executeUpdate();
            bd.desconectar();
    }
    
    public void insert(String nome, String telefone, Long cpf) throws SQLException, Exception{
            bd = new BancoDeDados();
            
            bd.conectar();
            bd.sql = "INSERT INTO \"Cliente\" VALUES(?,?,?)";
            bd.comandoSql = bd.connection.prepareStatement(bd.sql);
            bd.comandoSql.setLong(1, cpf);
            bd.comandoSql.setString(2, nome);
            bd.comandoSql.setString(3, telefone);
            bd.comandoSql.executeUpdate();
            bd.desconectar();
    }
    
    public ArrayList<Cliente> listarClientes() throws SQLException, Exception{
        
        ArrayList<Cliente> lista = new ArrayList<Cliente>();
        bd.conectar();
        bd.sql = "SELECT * FROM \"Cliente\" where \"cpf\" != 0";
        bd.comandoSql = bd.connection.prepareStatement(bd.sql);
        ResultSet rs = bd.comandoSql.executeQuery();
        
        Long cpf;
        String nome;
        String telefone;
        
        while(rs.next()){
            cpf = rs.getLong(1);
            nome = rs.getString(2);
            telefone = rs.getString(3);
            
            lista.add(new Cliente(nome,telefone,cpf));
            
        }        
        bd.desconectar();
        return lista;      
    }
    
    
}
